﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GA.Server.Api;
using System.Windows;
using GA.Server.DatabaseManager;
using System.Data.SqlClient;
using System.Threading;
using System.Reflection;
using System.Data;
using GA.Shared.Interfaces;
using System.Windows.Data;

namespace GA.Server.DatabaseManager
{
    public abstract class DatabaseObject : LinkedDependencyObject
    {
        public static T GetByParameters<T>(DatabaseManagerServer server, Dictionary<string, object> parameters) where T : DatabaseObject
        {
            if (parameters == null) throw new ArgumentNullException("parameters");

            T databaseObject = (T)typeof(T).GetConstructor(Type.EmptyTypes).Invoke(new object[] { });
            StringBuilder selectQuery = new StringBuilder();
            StringBuilder whereQuery = new StringBuilder();
            Dictionary<PropertyInfo, DatabaseFieldAttribute> properties = databaseObject.GetAllFieldInfo();
            foreach (KeyValuePair<PropertyInfo, DatabaseFieldAttribute> property in properties)
            {
                if (parameters.Any((parameter) => { return parameter.Key.Equals(property.Key.Name); }))
                {
                    KeyValuePair<string, object> parameter = parameters.First((findParameter) => { return findParameter.Key.Equals(property.Key.Name); });
                    if (whereQuery.Length > 0)
                    {
                        whereQuery.Append(" AND ");
                    }
                    if (property.Value.IsQuoted)
                    {
                        whereQuery.Append(string.Format("{0} = '{1}'", property.Value.FieldName, parameter.Value));
                    }
                    else
                    {
                        whereQuery.Append(string.Format("{0} = {1}", property.Value.FieldName, parameter.Value));
                    }
                }
                if (selectQuery.Length > 0)
                {
                    selectQuery.Append(",");
                }
                selectQuery.Append(property.Value.FieldName);
            }
            DataSet info = databaseObject.GetInfo(server, string.Format(sSelectDb, selectQuery.ToString(), databaseObject.TableName, whereQuery.ToString()));
            if (info.Tables.Count > 0 && info.Tables[0].Rows.Count > 0)
            {
                DataRow row = info.Tables[0].Rows[0];
                foreach (KeyValuePair<PropertyInfo, DatabaseFieldAttribute> getProperty in properties)
                {
                    if (getProperty.Key.PropertyType.IsAssignableFrom(typeof(Enum)))
                    {
                        getProperty.Key.SetValue(databaseObject, Enum.Parse(getProperty.Key.PropertyType, row[getProperty.Value.FieldName].ToString()), null);
                    }
                }
                return databaseObject;
            }
            return null;
        }

        public DatabaseObject()
        {
        }

        public DatabaseObject(LinkedDependencyObject baseObject)
            : base(baseObject)
        {
        }

        public DatabaseObject(LinkedDependencyObject baseObject, BindingMode bindingMode)
            : base(baseObject, bindingMode)
        {
        }

        public abstract void LoadAllPropertyObjects();
        public abstract void LoadPropertyObject(string propertyName);

        public void Update(DatabaseManagerServer server)
        {
            if (TableName == null || TableName.Length == 0) throw new InvalidOperationException("Table Name was not defined in this object.");
            KeyValuePair<SqlConnection, AutoResetEvent> connection = server.GetConnection(PoolName, TimeSpan.FromSeconds(60), TimeSpan.FromSeconds(5));

            StringBuilder whereQuery = new StringBuilder();
            Dictionary<PropertyInfo, DatabaseFieldAttribute> properties = GetAllFieldInfo();
            if (!properties.Any((property) => { return property.Value.IsIdentity; }))
            {
                throw new InvalidOperationException("No Identity is defined.");
            }
            foreach (KeyValuePair<PropertyInfo, DatabaseFieldAttribute> property in properties)
            {
                if (property.Value.IsIdentity)
                {
                    if (whereQuery.Length > 0)
                    {
                        whereQuery.Append(" AND ");
                    }
                    if (property.Value.IsQuoted)
                    {
                        whereQuery.Append(string.Format("{0} = '{1}'", property.Value.FieldName, property.Key.GetValue(this, null)));
                    }
                    else
                    {
                        whereQuery.Append(string.Format("{0} = {1}", property.Value.FieldName, property.Key.GetValue(this, null)));
                    }
                }
            }
            
        }

        public void Delete(DatabaseManagerServer server)
        {
            if (TableName == null || TableName.Length == 0) throw new InvalidOperationException("Table Name was not defined in this object.");

            StringBuilder whereQuery = new StringBuilder();
            foreach (KeyValuePair<PropertyInfo, DatabaseFieldAttribute> property in GetAllFieldInfo())
            {
                if (property.Value.IsKey)
                {
                    if (whereQuery.Length > 0)
                    {
                        whereQuery.Append(" AND ");
                    }
                    if (property.Value.IsQuoted)
                    {
                        whereQuery.Append(string.Format("{0} = '{1}'", property.Value.FieldName, property.Key.GetValue(this, null)));
                    }
                    else
                    {
                        whereQuery.Append(string.Format("{0} = {1}", property.Value.FieldName, property.Key.GetValue(this, null)));
                    }
                }
            }
            string queryText = string.Format(sDeleteDb, TableName, whereQuery.ToString());
            
        }

        public void Create(DatabaseManagerServer server)
        {
            if (TableName == null || TableName.Length == 0) throw new InvalidOperationException("Table Name was not defined in this object.");

            StringBuilder fieldQuery = new StringBuilder();
            StringBuilder valueQuery = new StringBuilder();
            StringBuilder identityQuery = new StringBuilder();
            StringBuilder identityFieldQuery = new StringBuilder();
            bool hasIdentity = false;
            Dictionary<PropertyInfo, DatabaseFieldAttribute> properties = GetAllFieldInfo();
            foreach (KeyValuePair<PropertyInfo, DatabaseFieldAttribute> propertyInfo in properties)
            {
                if (!propertyInfo.Value.IsIdentity)
                {
                    if (fieldQuery.Length > 0)
                    {
                        fieldQuery.Append(",");
                    }
                    fieldQuery.Append(propertyInfo.Value.FieldName);

                    if (valueQuery.Length > 0)
                    {
                        valueQuery.Append(",");
                    }
                    if (propertyInfo.Value.IsQuoted && propertyInfo.Key.GetValue(this, null) != null)
                    {
                        valueQuery.Append(string.Format("'{0}'", propertyInfo.Key.GetValue(this, null)));
                    }
                    else if (propertyInfo.Key.GetValue(this, null) == null)
                    {
                        valueQuery.Append("NULL");
                    }
                    else
                    {
                        valueQuery.Append(propertyInfo.Key.GetValue(this, null));
                    }

                    if (identityFieldQuery.Length > 0)
                    {
                        identityFieldQuery.Append(" AND ");
                    }
                    if (propertyInfo.Value.IsQuoted && propertyInfo.Key.GetValue(this, null) != null)
                    {
                        identityFieldQuery.Append(string.Format("{0} = '{1}'", propertyInfo.Value.FieldName, propertyInfo.Key.GetValue(this, null)));
                    }
                    else if (propertyInfo.Key.GetValue(this, null) == null)
                    {
                        identityFieldQuery.Append(string.Format("{0} = NULL", propertyInfo.Value.FieldName));
                    }
                    else
                    {
                        identityFieldQuery.Append(string.Format("{0} = {1}", propertyInfo.Value.FieldName, propertyInfo.Key.GetValue(this, null)));
                    }
                }
                else
                {
                    hasIdentity = true;
                    if (identityQuery.Length > 0)
                    {
                        identityQuery.Append(",");
                    }
                    identityQuery.Append(propertyInfo.Value.FieldName);
                }
            }
            ExecuteQuery(server, string.Format(sInsertDb, TableName, fieldQuery.ToString(), valueQuery.ToString()));
            if (hasIdentity)
            {
                DataSet identities = GetInfo(server, string.Format(sSelectDb, identityQuery.ToString(), TableName, identityFieldQuery));
                if (identities.Tables.Count > 0 && identities.Tables[0].Rows.Count > 0)
                {
                    DataRow dataRow = identities.Tables[0].Rows[0];
                    foreach (KeyValuePair<PropertyInfo, DatabaseFieldAttribute> propertyInfo in properties.TakeWhile((property) => { return property.Value.IsIdentity; }))
                    {
                        propertyInfo.Key.SetValue(this, dataRow[propertyInfo.Value.FieldName], null);
                    }
                }
            }
        }

        public object GetCustomField(DatabaseManagerServer server, string fieldName)
        {
            if (TableName == null || TableName.Length == 0) throw new InvalidOperationException("Table Name was not defined in this object.");
            if (fieldName == null) throw new ArgumentNullException("fieldName");
            if (fieldName.Length == 0) throw new ArgumentException("fieldName cannot be empty.");

            StringBuilder whereQuery = new StringBuilder();
            foreach (KeyValuePair<PropertyInfo, DatabaseFieldAttribute> property in GetAllFieldInfo())
            {
                if (property.Value.IsKey)
                {
                    if (whereQuery.Length > 0)
                    {
                        whereQuery.Append(" AND ");
                    }
                    if (property.Value.IsQuoted)
                    {
                        whereQuery.Append(string.Format("{0} = '{1}'", property.Value.FieldName, property.Key.GetValue(this, null)));
                    }
                    else
                    {
                        whereQuery.Append(string.Format("{0} = {1}", property.Value.FieldName, property.Key.GetValue(this, null)));
                    }
                }
            }
            DataSet dataSet = GetInfo(server, string.Format(sSelectDb, fieldName, TableName, whereQuery.ToString()));
            if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
            {
                return dataSet.Tables[0].Rows[0][fieldName];
            }
            return null;
        }

        public void SetCustomField(DatabaseManagerServer server, string fieldName, object value, bool isQuoted)
        {
            if (TableName == null || TableName.Length == 0) throw new InvalidOperationException("Table Name was not defined in this object.");
            if (fieldName == null) throw new ArgumentNullException("fieldName");
            if (fieldName.Length == 0) throw new ArgumentException("fieldName cannot be empty.");

            StringBuilder whereQuery = new StringBuilder();
            foreach (KeyValuePair<PropertyInfo, DatabaseFieldAttribute> property in GetAllFieldInfo())
            {
                if (property.Value.IsKey)
                {
                    if (whereQuery.Length > 0)
                    {
                        whereQuery.Append(" AND ");
                    }
                    if (property.Value.IsQuoted)
                    {
                        whereQuery.Append(string.Format("{0} = '{1}'", property.Value.FieldName, property.Key.GetValue(this, null)));
                    }
                    else
                    {
                        whereQuery.Append(string.Format("{0} = {1}", property.Value.FieldName, property.Key.GetValue(this, null)));
                    }
                }
            }
            ExecuteQuery(server, string.Format(sUpdateDb, TableName, isQuoted && value != null ? string.Format("{0} = '{1}'", fieldName, value) : string.Format("{0} = {1}", fieldName, value == null ? "NULL" : value), whereQuery.ToString()));
        }

        protected virtual string TableName { get { return GetType().Name.Replace("Database", ""); } }
        protected virtual string PoolName { get { return GetType().Name.Replace("Database", ""); } }

        private const string sUpdateDb = "UPDATE {0} SET {1} WHERE {2}";
        private const string sInsertDb = "INSERT INTO {0} ({1}) VALUES ({2})";
        private const string sDeleteDb = "DELETE FROM {0} WHERE {1}";
        private const string sSelectDb = "SELECT {0} FROM {1} WHERE {2}";

        private void ExecuteQuery(DatabaseManagerServer server, string query)
        {
            KeyValuePair<SqlConnection, AutoResetEvent> connection = server.GetConnection(PoolName, TimeSpan.FromSeconds(60), TimeSpan.FromSeconds(5));
            connection.Key.ExecuteNonQuery(query, 30);
            connection.Value.Set();
        }

        private DataSet GetInfo(DatabaseManagerServer server, string query)
        {
            KeyValuePair<SqlConnection, AutoResetEvent> connection = server.GetConnection(PoolName, TimeSpan.FromSeconds(60), TimeSpan.FromSeconds(5));
            DataSet dataSet = connection.Key.Query(query, 30);
            connection.Value.Set();
            return dataSet;
        }

        private List<string> GetAllFields()
        {
            List<string> fields = new List<string>();
            foreach (PropertyInfo property in GetType().GetProperties())
            {
                Attribute attribute = Attribute.GetCustomAttribute(property, typeof(DatabaseFieldAttribute));
                if (attribute != null)
                {
                    fields.Add(((DatabaseFieldAttribute)attribute).FieldName);
                }
            }
            return fields;
        }

        private string GetFieldName(string propertyName)
        {
            PropertyInfo property = GetType().GetProperty(propertyName);
            if (property != null)
            {
                Attribute attribute = Attribute.GetCustomAttribute(property, typeof(DatabaseFieldAttribute));
                if (attribute != null)
                {
                    return ((DatabaseFieldAttribute)attribute).FieldName;
                }
            }
            return null;
        }

        private bool? GetFieldIsQuoted(string propertyName)
        {
            PropertyInfo property = GetType().GetProperty(propertyName);
            if (property != null)
            {
                Attribute attribute = Attribute.GetCustomAttribute(property, typeof(DatabaseFieldAttribute));
                if (attribute != null)
                {
                    return ((DatabaseFieldAttribute)attribute).IsQuoted;
                }
            }
            return null;
        }

        private Dictionary<PropertyInfo, DatabaseFieldAttribute> GetAllFieldInfo()
        {
            Dictionary<PropertyInfo, DatabaseFieldAttribute> fields = new Dictionary<PropertyInfo, DatabaseFieldAttribute>();
            foreach (PropertyInfo property in GetType().GetProperties())
            {
                Attribute attribute = Attribute.GetCustomAttribute(property, typeof(DatabaseFieldAttribute));
                if (attribute != null)
                {
                    fields.Add(property, ((DatabaseFieldAttribute)attribute));
                }
            }
            return fields;
        }

    }
}
